{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 主题：京东用户购买意向预测"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 故事背景：\n",
    "\n",
    "京东作为中国最大的自营式电商，在保持高速发展的同时，沉淀了数亿的忠实用户，积累了海量的真实数据。如何从历史数据中找出规律，去预测用户未来的购买需求，让最合适的商品遇见最需要的人，是大数据应用在精准营销中的关键问题，也是所有电商平台在做智能化升级时所需要的核心技术。\n",
    "以京东商城真实的用户、商品和行为数据（脱敏后）为基础，通过数据挖掘的技术和机器学习的算法，构建用户购买商品的预测模型，输出高潜用户和目标商品的匹配结果，为精准营销提供高质量的目标群体。\n",
    "\n",
    "目标：使用京东多个品类下商品的历史销售数据，构建算法模型，预测用户在未来5天内，对某个目标品类下商品的购买意向。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据集：\n",
    "\n",
    "- 这里涉及到的数据集是京东最新的数据集：\n",
    "- JData_User.csv 用户数据集 105,321个用户\n",
    "- JData_Comment.csv 商品评论 558,552条记录\n",
    "- JData_Product.csv 预测商品集合 24,187条记录\n",
    "- JData_Action_201602.csv 2月份行为交互记录 11,485,424条记录\n",
    "- JData_Action_201603.csv 3月份行为交互记录 25,916,378条记录\n",
    "- JData_Action_201604.csv 4月份行为交互记录 13,199,934条记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"img/1.png\" alt=\"FAO\" width=\"390\" >\n",
    "<img src=\"img/2.png\" alt=\"FAO\" width=\"390\" >\n",
    "<img src=\"img/3.png\" alt=\"FAO\" width=\"390\" >\n",
    "<img src=\"img/5.png\" alt=\"FAO\" width=\"390\" >"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据挖掘流程：\n",
    "（一）.数据清洗\n",
    "    1. 数据集完整性验证\n",
    "    2. 数据集中是否存在缺失值\n",
    "    3. 数据集中各特征数值应该如何处理\n",
    "    4. 哪些数据是我们想要的，哪些是可以过滤掉的\n",
    "    5. 将有价值数据信息做成新的数据源\n",
    "    6. 去除无行为交互的商品和用户\n",
    "    7. 去掉浏览量很大而购买量很少的用户(惰性用户或爬虫用户)\n",
    "（二）.数据理解与分析\n",
    "    1. 掌握各个特征的含义\n",
    "    2. 观察数据有哪些特点，是否可利用来建模\n",
    "    3. 可视化展示便于分析\n",
    "    4. 用户的购买意向是否随着时间等因素变化\n",
    "（三）.特征提取\n",
    "    1. 基于清洗后的数据集哪些特征是有价值\n",
    "    2. 分别对用户与商品以及其之间构成的行为进行特征提取\n",
    "    3. 行为因素中哪些是核心？如何提取？\n",
    "    4. 瞬时行为特征or累计行为特征？\n",
    "\n",
    "（四）.模型建立\n",
    "    1. 使用机器学习算法进行预测 \n",
    "    2. 参数设置与调节\n",
    "    3. 数据集切分？\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据集验证"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 首先检查JData_User中的用户和JData_Action中的用户是否一致\n",
    "保证行为数据中的所产生的行为均由用户数据中的用户产生（但是可能存在用户在行为数据中无行为）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "思路：利用pd.Merge连接sku 和 Action中的sku, 观察Action中的数据是否减少\n",
    "Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  sku  data\n",
      "0   a     1\n",
      "1   a     1\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# test sample\n",
    "df1 = pd.DataFrame({'sku':['a','a','e','c'],'data':[1,1,2,3]})\n",
    "df2 = pd.DataFrame({'sku':['a','b','f']})\n",
    "df3 = pd.DataFrame({'sku':['a','b','d']})\n",
    "df4 = pd.DataFrame({'sku':['a','b','c','d']})\n",
    "print (pd.merge(df2,df1))\n",
    "#print (pd.merge(df1,df2))\n",
    "#print (pd.merge(df3,df1))\n",
    "#print (pd.merge(df4,df1))\n",
    "#print (pd.merge(df1,df3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Is action of Feb. from User file?  True\n",
      "Is action of Mar. from User file?  True\n",
      "Is action of Apr. from User file?  True\n"
     ]
    }
   ],
   "source": [
    "def user_action_check():\n",
    "    df_user = pd.read_csv('data/JData_User.csv',encoding='gbk')\n",
    "    df_sku = df_user.loc[:,'user_id'].to_frame()\n",
    "    df_month2 = pd.read_csv('data/JData_Action_201602.csv',encoding='gbk')\n",
    "    print ('Is action of Feb. from User file? ', len(df_month2) == len(pd.merge(df_sku,df_month2)))\n",
    "    df_month3 = pd.read_csv('data/JData_Action_201603.csv',encoding='gbk')\n",
    "    print ('Is action of Mar. from User file? ', len(df_month3) == len(pd.merge(df_sku,df_month3)))\n",
    "    df_month4 = pd.read_csv('data/JData_Action_201604.csv',encoding='gbk')\n",
    "    print ('Is action of Apr. from User file? ', len(df_month4) == len(pd.merge(df_sku,df_month4)))\n",
    "\n",
    "user_action_check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "结论： User数据集中的用户和交互行为数据集中的用户完全一致\n",
    "\n",
    "*根据merge前后的数据量比对，能保证Action中的用户ID是User中的ID的子集*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 检查是否有重复记录\n",
    "除去各个数据文件中完全重复的记录,可能解释是重复数据是有意义的，比如用户同时购买多件商品，同时添加多个数量的商品到购物车等..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def deduplicate(filepath, filename, newpath):\n",
    "    df_file = pd.read_csv(filepath,encoding='gbk')       \n",
    "    before = df_file.shape[0]\n",
    "    df_file.drop_duplicates(inplace=True)\n",
    "    after = df_file.shape[0]\n",
    "    n_dup = before-after\n",
    "    print ('No. of duplicate records for ' + filename + ' is: ' + str(n_dup))\n",
    "    if n_dup != 0:\n",
    "        df_file.to_csv(newpath, index=None)\n",
    "    else:\n",
    "        print ('no duplicate records in ' + filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No. of duplicate records for Mar. action is: 7085038\n",
      "No. of duplicate records for Feb. action is: 3672710\n",
      "No. of duplicate records for Comment is: 0\n",
      "no duplicate records in Comment\n",
      "No. of duplicate records for Product is: 0\n",
      "no duplicate records in Product\n",
      "No. of duplicate records for User is: 0\n",
      "no duplicate records in User\n"
     ]
    }
   ],
   "source": [
    "# deduplicate('data/JData_Action_201602.csv', 'Feb. action', 'data/JData_Action_201602_dedup.csv')\n",
    "deduplicate('data/JData_Action_201603.csv', 'Mar. action', 'data/JData_Action_201603_dedup.csv')\n",
    "deduplicate('data/JData_Action_201604.csv', 'Feb. action', 'data/JData_Action_201604_dedup.csv')\n",
    "deduplicate('data/JData_Comment.csv', 'Comment', 'data/JData_Comment_dedup.csv')\n",
    "deduplicate('data/JData_Product.csv', 'Product', 'data/JData_Product_dedup.csv')\n",
    "deduplicate('data/JData_User.csv', 'User', 'data/JData_User_dedup.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>sku_id</th>\n",
       "      <th>time</th>\n",
       "      <th>model_id</th>\n",
       "      <th>cate</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2176378</td>\n",
       "      <td>2176378</td>\n",
       "      <td>2176378</td>\n",
       "      <td>0</td>\n",
       "      <td>2176378</td>\n",
       "      <td>2176378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>636</td>\n",
       "      <td>636</td>\n",
       "      <td>636</td>\n",
       "      <td>0</td>\n",
       "      <td>636</td>\n",
       "      <td>636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1464</td>\n",
       "      <td>1464</td>\n",
       "      <td>1464</td>\n",
       "      <td>0</td>\n",
       "      <td>1464</td>\n",
       "      <td>1464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1981</td>\n",
       "      <td>1981</td>\n",
       "      <td>1981</td>\n",
       "      <td>0</td>\n",
       "      <td>1981</td>\n",
       "      <td>1981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>575597</td>\n",
       "      <td>575597</td>\n",
       "      <td>575597</td>\n",
       "      <td>545054</td>\n",
       "      <td>575597</td>\n",
       "      <td>575597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id   sku_id     time  model_id     cate    brand\n",
       "type                                                       \n",
       "1     2176378  2176378  2176378         0  2176378  2176378\n",
       "2         636      636      636         0      636      636\n",
       "3        1464     1464     1464         0     1464     1464\n",
       "4          37       37       37         0       37       37\n",
       "5        1981     1981     1981         0     1981     1981\n",
       "6      575597   575597   575597    545054   575597   575597"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month2 = pd.read_csv('data/JData_Action_201602.csv',encoding='gbk')\n",
    "IsDuplicated = df_month2.duplicated() \n",
    "df_d=df_month2[IsDuplicated]\n",
    "df_d.groupby('type').count()               #发现重复数据大多数都是由于浏览（1），或者点击(6)产生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 检查是否存在注册时间在2016年-4月-15号之后的用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>user_reg_tm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7457</th>\n",
       "      <td>207458</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7463</th>\n",
       "      <td>207464</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7467</th>\n",
       "      <td>207468</td>\n",
       "      <td>36-45岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7472</th>\n",
       "      <td>207473</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7482</th>\n",
       "      <td>207483</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7492</th>\n",
       "      <td>207493</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7493</th>\n",
       "      <td>207494</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7503</th>\n",
       "      <td>207504</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7510</th>\n",
       "      <td>207511</td>\n",
       "      <td>46-55岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7512</th>\n",
       "      <td>207513</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7518</th>\n",
       "      <td>207519</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7521</th>\n",
       "      <td>207522</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7525</th>\n",
       "      <td>207526</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7533</th>\n",
       "      <td>207534</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7543</th>\n",
       "      <td>207544</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7544</th>\n",
       "      <td>207545</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551</th>\n",
       "      <td>207552</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7553</th>\n",
       "      <td>207554</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8545</th>\n",
       "      <td>208546</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-04-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9394</th>\n",
       "      <td>209395</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-05-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10362</th>\n",
       "      <td>210363</td>\n",
       "      <td>56岁以上</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-05-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10367</th>\n",
       "      <td>210368</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-05-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11019</th>\n",
       "      <td>211020</td>\n",
       "      <td>36-45岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12014</th>\n",
       "      <td>212015</td>\n",
       "      <td>36-45岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-07-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13850</th>\n",
       "      <td>213851</td>\n",
       "      <td>26-35岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-09-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14542</th>\n",
       "      <td>214543</td>\n",
       "      <td>-1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16746</th>\n",
       "      <td>216747</td>\n",
       "      <td>16-25岁</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-11-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id     age  sex  user_lv_cd user_reg_tm\n",
       "7457    207458      -1  2.0           1  2016-04-15\n",
       "7463    207464  26-35岁  2.0           2  2016-04-15\n",
       "7467    207468  36-45岁  2.0           3  2016-04-15\n",
       "7472    207473      -1  2.0           1  2016-04-15\n",
       "7482    207483  26-35岁  2.0           3  2016-04-15\n",
       "7492    207493  16-25岁  2.0           3  2016-04-15\n",
       "7493    207494  16-25岁  2.0           3  2016-04-15\n",
       "7503    207504  16-25岁  2.0           4  2016-04-15\n",
       "7510    207511  46-55岁  2.0           5  2016-04-15\n",
       "7512    207513      -1  2.0           1  2016-04-15\n",
       "7518    207519  26-35岁  2.0           2  2016-04-15\n",
       "7521    207522  26-35岁  0.0           3  2016-04-15\n",
       "7525    207526      -1  2.0           3  2016-04-15\n",
       "7533    207534      -1  2.0           1  2016-04-15\n",
       "7543    207544  26-35岁  2.0           3  2016-04-15\n",
       "7544    207545      -1  2.0           1  2016-04-15\n",
       "7551    207552  26-35岁  2.0           3  2016-04-15\n",
       "7553    207554  16-25岁  2.0           4  2016-04-15\n",
       "8545    208546  16-25岁  0.0           2  2016-04-29\n",
       "9394    209395  16-25岁  1.0           2  2016-05-11\n",
       "10362   210363   56岁以上  2.0           2  2016-05-24\n",
       "10367   210368      -1  2.0           1  2016-05-24\n",
       "11019   211020  36-45岁  2.0           3  2016-06-06\n",
       "12014   212015  36-45岁  2.0           2  2016-07-05\n",
       "13850   213851  26-35岁  2.0           3  2016-09-11\n",
       "14542   214543      -1  2.0           1  2016-10-05\n",
       "16746   216747  16-25岁  2.0           1  2016-11-25"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_user = pd.read_csv('data\\JData_User.csv',encoding='gbk')\n",
    "df_user['user_reg_tm']=pd.to_datetime(df_user['user_reg_tm'])\n",
    "df_user.loc[df_user.user_reg_tm  >= '2016-4-15']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于注册时间是京东系统错误造成，如果行为数据中没有在4月15号之后的数据的话，那么说明这些用户还是正常用户，并不需要删除。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>sku_id</th>\n",
       "      <th>time</th>\n",
       "      <th>model_id</th>\n",
       "      <th>type</th>\n",
       "      <th>cate</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, sku_id, time, model_id, type, cate, brand]\n",
       "Index: []"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month = pd.read_csv('data\\JData_Action_201604.csv')\n",
    "df_month['time'] = pd.to_datetime(df_month['time'])\n",
    "df_month.loc[df_month.time >= '2016-4-16']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "结论：说明用户没有异常操作数据，所以这一批用户不删除"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 行为数据中的user_id为浮点型，进行INT类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "int64\n",
      "int64\n",
      "int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_month = pd.read_csv('data\\JData_Action_201602.csv',encoding='gbk')\n",
    "df_month['user_id'] = df_month['user_id'].apply(lambda x:int(x))\n",
    "print (df_month['user_id'].dtype)\n",
    "df_month.to_csv('data\\JData_Action_201602.csv',index=None)\n",
    "df_month = pd.read_csv('data\\JData_Action_201603.csv',encoding='gbk')\n",
    "df_month['user_id'] = df_month['user_id'].apply(lambda x:int(x))\n",
    "print (df_month['user_id'].dtype)\n",
    "df_month.to_csv('data\\JData_Action_201603.csv',index=None)\n",
    "df_month = pd.read_csv('data\\JData_Action_201604.csv',encoding='gbk')\n",
    "df_month['user_id'] = df_month['user_id'].apply(lambda x:int(x))\n",
    "print (df_month['user_id'].dtype)\n",
    "df_month.to_csv('data\\JData_Action_201604.csv',index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 年龄区间的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     user_id    sex  user_lv_cd  user_reg_tm\n",
      "age                                         \n",
      "-1     14412  14412       14412        14412\n",
      "1          7      7           7            7\n",
      "2       8797   8797        8797         8797\n",
      "3      46570  46570       46570        46570\n",
      "4      30336  30336       30336        30336\n",
      "5       3325   3325        3325         3325\n",
      "6       1871   1871        1871         1871\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_user = pd.read_csv('data\\JData_User.csv',encoding='gbk')\n",
    "\n",
    "def tranAge(x):\n",
    "    if x == u'15岁以下':\n",
    "        x='1'\n",
    "    elif x==u'16-25岁':\n",
    "        x='2'\n",
    "    elif x==u'26-35岁':\n",
    "        x='3'\n",
    "    elif x==u'36-45岁':\n",
    "        x='4'\n",
    "    elif x==u'46-55岁':\n",
    "        x='5'\n",
    "    elif x==u'56岁以上':\n",
    "        x='6'\n",
    "    return x\n",
    "df_user['age']=df_user['age'].apply(tranAge)\n",
    "print (df_user.groupby(df_user['age']).count())\n",
    "df_user.to_csv('data\\JData_User.csv',index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "为了能够进行上述清洗,在此首先构造了简单的用户(user)行为特征和商品(item)行为特征,对应于两张表user_table和\n",
    "### user_table\n",
    "-    user_table特征包括:\n",
    "-    user_id(用户id),age(年龄),sex(性别),\n",
    "-    user_lv_cd(用户级别),browse_num(浏览数),\n",
    "-    addcart_num(加购数),delcart_num(删购数),\n",
    "-    buy_num(购买数),favor_num(收藏数),\n",
    "-    click_num(点击数),buy_addcart_ratio(购买加购转化率),\n",
    "-    buy_browse_ratio(购买浏览转化率),\n",
    "-    buy_click_ratio(购买点击转化率),\n",
    "-    buy_favor_ratio(购买收藏转化率)\n",
    "    \n",
    "### item_table特征包括:\n",
    "-    sku_id(商品id),attr1,attr2,\n",
    "-    attr3,cate,brand,browse_num,\n",
    "-    addcart_num,delcart_num,\n",
    "-    buy_num,favor_num,click_num,\n",
    "-    buy_addcart_ratio,buy_browse_ratio,\n",
    "-    buy_click_ratio,buy_favor_ratio,\n",
    "-    comment_num(评论数),\n",
    "-    has_bad_comment(是否有差评),\n",
    "-    bad_comment_rate(差评率)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 构建User_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#定义文件名\n",
    "ACTION_201602_FILE = \"data/JData_Action_201602.csv\"\n",
    "ACTION_201603_FILE = \"data/JData_Action_201603.csv\"\n",
    "ACTION_201604_FILE = \"data/JData_Action_201604.csv\"\n",
    "COMMENT_FILE = \"data/JData_Comment.csv\"\n",
    "PRODUCT_FILE = \"data/JData_Product.csv\"\n",
    "USER_FILE = \"data/JData_User.csv\"\n",
    "USER_TABLE_FILE = \"data/User_table.csv\"\n",
    "ITEM_TABLE_FILE = \"data/Item_table.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 导入相关包\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 功能函数: 对每一个user分组的数据进行统计\n",
    "def add_type_count(group):\n",
    "    behavior_type = group.type.astype(int)\n",
    "    # 用户行为类别\n",
    "    type_cnt = Counter(behavior_type)\n",
    "    # 1: 浏览 2: 加购 3: 删除\n",
    "    # 4: 购买 5: 收藏 6: 点击\n",
    "    group['browse_num'] = type_cnt[1]\n",
    "    group['addcart_num'] = type_cnt[2]\n",
    "    group['delcart_num'] = type_cnt[3]\n",
    "    group['buy_num'] = type_cnt[4]\n",
    "    group['favor_num'] = type_cnt[5]\n",
    "    group['click_num'] = type_cnt[6]\n",
    "\n",
    "    return group[['user_id', 'browse_num', 'addcart_num',\n",
    "                  'delcart_num', 'buy_num', 'favor_num',\n",
    "                  'click_num']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于用户行为数据量较大,一次性读入可能造成内存错误(Memory Error),因而使用pandas的分块(chunk)读取."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对action数据进行统计\n",
    "#根据自己调节chunk_size大小\n",
    "def get_from_action_data(fname, chunk_size=50000):\n",
    "    reader = pd.read_csv(fname, header=0, iterator=True,encoding='gbk')\n",
    "    chunks = []\n",
    "    loop = True\n",
    "    while loop:\n",
    "        try:\n",
    "            # 只读取user_id和type两个字段\n",
    "            chunk = reader.get_chunk(chunk_size)[[\"user_id\", \"type\"]]\n",
    "            chunks.append(chunk)\n",
    "        except StopIteration:\n",
    "            loop = False\n",
    "            print(\"Iteration is stopped\")\n",
    "    # 将块拼接为pandas dataframe格式\n",
    "    df_ac = pd.concat(chunks, ignore_index=True)\n",
    "    # 按user_id分组，对每一组进行统计，as_index 表示无索引形式返回数据\n",
    "    df_ac = df_ac.groupby(['user_id'], as_index=False).apply(add_type_count)\n",
    "    # 将重复的行丢弃\n",
    "    df_ac = df_ac.drop_duplicates('user_id')\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 将各个action数据的统计量进行聚合\n",
    "def merge_action_data():\n",
    "    df_ac = []\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201602_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201603_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201604_FILE))\n",
    "\n",
    "    df_ac = pd.concat(df_ac, ignore_index=True)\n",
    "    # 用户在不同action表中统计量求和\n",
    "    df_ac = df_ac.groupby(['user_id'], as_index=False).sum()\n",
    "    #　构造转化率字段\n",
    "    df_ac['buy_addcart_ratio'] = df_ac['buy_num'] / df_ac['addcart_num']\n",
    "    df_ac['buy_browse_ratio'] = df_ac['buy_num'] / df_ac['browse_num']\n",
    "    df_ac['buy_click_ratio'] = df_ac['buy_num'] / df_ac['click_num']\n",
    "    df_ac['buy_favor_ratio'] = df_ac['buy_num'] / df_ac['favor_num']\n",
    "    \n",
    "    # 将大于１的转化率字段置为１(100%)\n",
    "    df_ac.ix[df_ac['buy_addcart_ratio'] > 1., 'buy_addcart_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_browse_ratio'] > 1., 'buy_browse_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_click_ratio'] > 1., 'buy_click_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_favor_ratio'] > 1., 'buy_favor_ratio'] = 1.\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#　从FJData_User表中抽取需要的字段\n",
    "def get_from_jdata_user():\n",
    "    df_usr = pd.read_csv(USER_FILE, header=0)\n",
    "    df_usr = df_usr[[\"user_id\", \"age\", \"sex\", \"user_lv_cd\"]]\n",
    "    return df_usr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:18: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "user_base = get_from_jdata_user()\n",
    "user_behavior = merge_action_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接成一张表，类似于SQL的左连接(left join)\n",
    "user_behavior = pd.merge(user_base, user_behavior, on=['user_id'], how='left')\n",
    "# 保存为user_table.csv\n",
    "user_behavior.to_csv(USER_TABLE_FILE, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>212.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>0.045455</td>\n",
       "      <td>0.004717</td>\n",
       "      <td>0.002415</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200002</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>238.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>484.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200003</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>221.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200004</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200005</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>106.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.009434</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  age  sex  user_lv_cd  browse_num  addcart_num  delcart_num  \\\n",
       "0   200001  6.0  2.0           5       212.0         22.0         13.0   \n",
       "1   200002 -1.0  0.0           1       238.0          1.0          0.0   \n",
       "2   200003  4.0  1.0           4       221.0          4.0          1.0   \n",
       "3   200004 -1.0  2.0           1        52.0          0.0          0.0   \n",
       "4   200005  2.0  0.0           4       106.0          2.0          3.0   \n",
       "\n",
       "   buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "0      1.0        0.0      414.0           0.045455          0.004717   \n",
       "1      0.0        0.0      484.0           0.000000          0.000000   \n",
       "2      0.0        1.0      420.0           0.000000          0.000000   \n",
       "3      0.0        0.0       61.0                NaN          0.000000   \n",
       "4      1.0        2.0      161.0           0.500000          0.009434   \n",
       "\n",
       "   buy_click_ratio  buy_favor_ratio  \n",
       "0         0.002415              1.0  \n",
       "1         0.000000              NaN  \n",
       "2         0.000000              0.0  \n",
       "3         0.000000              NaN  \n",
       "4         0.006211              0.5  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table = pd.read_csv(USER_TABLE_FILE)\n",
    "user_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 构建Item_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#定义文件名\n",
    "ACTION_201602_FILE = \"data/JData_Action_201602.csv\"\n",
    "ACTION_201603_FILE = \"data/JData_Action_201603.csv\"\n",
    "ACTION_201604_FILE = \"data/JData_Action_201604.csv\"\n",
    "COMMENT_FILE = \"data/JData_Comment.csv\"\n",
    "PRODUCT_FILE = \"data/JData_Product.csv\"\n",
    "USER_FILE = \"data/JData_User.csv\"\n",
    "USER_TABLE_FILE = \"data/User_table.csv\"\n",
    "ITEM_TABLE_FILE = \"data/Item_table.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 导入相关包\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 读取Product中商品\n",
    "def get_from_jdata_product():\n",
    "    df_item = pd.read_csv(PRODUCT_FILE, header=0,encoding='gbk')\n",
    "    return df_item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 对每一个商品分组进行统计\n",
    "def add_type_count(group):\n",
    "    behavior_type = group.type.astype(int)\n",
    "    type_cnt = Counter(behavior_type)\n",
    "\n",
    "    group['browse_num'] = type_cnt[1]\n",
    "    group['addcart_num'] = type_cnt[2]\n",
    "    group['delcart_num'] = type_cnt[3]\n",
    "    group['buy_num'] = type_cnt[4]\n",
    "    group['favor_num'] = type_cnt[5]\n",
    "    group['click_num'] = type_cnt[6]\n",
    "\n",
    "    return group[['sku_id', 'browse_num', 'addcart_num',\n",
    "                  'delcart_num', 'buy_num', 'favor_num',\n",
    "                  'click_num']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#对action中的数据进行统计\n",
    "def get_from_action_data(fname, chunk_size=50000):\n",
    "    reader = pd.read_csv(fname, header=0, iterator=True)\n",
    "    chunks = []\n",
    "    loop = True\n",
    "    while loop:\n",
    "        try:\n",
    "            chunk = reader.get_chunk(chunk_size)[[\"sku_id\", \"type\"]]\n",
    "            chunks.append(chunk)\n",
    "        except StopIteration:\n",
    "            loop = False\n",
    "            print(\"Iteration is stopped\")\n",
    "\n",
    "    df_ac = pd.concat(chunks, ignore_index=True)\n",
    "\n",
    "    df_ac = df_ac.groupby(['sku_id'], as_index=False).apply(add_type_count)\n",
    "    # Select unique row\n",
    "    df_ac = df_ac.drop_duplicates('sku_id')\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 获取评论中的商品数据,如果存在某一个商品有两个日期的评论，我们取最晚的那一个\n",
    "def get_from_jdata_comment():\n",
    "    df_cmt = pd.read_csv(COMMENT_FILE, header=0)\n",
    "    df_cmt['dt'] = pd.to_datetime(df_cmt['dt'])\n",
    "    # find latest comment index\n",
    "    idx = df_cmt.groupby(['sku_id'])['dt'].transform(max) == df_cmt['dt']\n",
    "    df_cmt = df_cmt[idx]\n",
    "\n",
    "    return df_cmt[['sku_id', 'comment_num',\n",
    "                   'has_bad_comment', 'bad_comment_rate']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def merge_action_data():\n",
    "    df_ac = []\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201602_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201603_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201604_FILE))\n",
    "\n",
    "    df_ac = pd.concat(df_ac, ignore_index=True)\n",
    "    df_ac = df_ac.groupby(['sku_id'], as_index=False).sum()\n",
    "\n",
    "    df_ac['buy_addcart_ratio'] = df_ac['buy_num'] / df_ac['addcart_num']\n",
    "    df_ac['buy_browse_ratio'] = df_ac['buy_num'] / df_ac['browse_num']\n",
    "    df_ac['buy_click_ratio'] = df_ac['buy_num'] / df_ac['click_num']\n",
    "    df_ac['buy_favor_ratio'] = df_ac['buy_num'] / df_ac['favor_num']\n",
    "\n",
    "    df_ac.ix[df_ac['buy_addcart_ratio'] > 1., 'buy_addcart_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_browse_ratio'] > 1., 'buy_browse_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_click_ratio'] > 1., 'buy_click_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_favor_ratio'] > 1., 'buy_favor_ratio'] = 1.\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:15: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "\n",
    "item_base = get_from_jdata_product()\n",
    "item_behavior = merge_action_data()\n",
    "item_comment = get_from_jdata_comment()\n",
    "\n",
    "# SQL: left join\n",
    "item_behavior = pd.merge(\n",
    "    item_base, item_behavior, on=['sku_id'], how='left')\n",
    "item_behavior = pd.merge(\n",
    "    item_behavior, item_comment, on=['sku_id'], how='left')\n",
    "\n",
    "item_behavior.to_csv(ITEM_TABLE_FILE, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sku_id</th>\n",
       "      <th>a1</th>\n",
       "      <th>a2</th>\n",
       "      <th>a3</th>\n",
       "      <th>cate</th>\n",
       "      <th>brand</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "      <th>comment_num</th>\n",
       "      <th>has_bad_comment</th>\n",
       "      <th>bad_comment_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>489</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>489</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100006</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>545</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>244</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sku_id  a1  a2  a3  cate  brand  browse_num  addcart_num  delcart_num  \\\n",
       "0      10   3   1   1     8    489         NaN          NaN          NaN   \n",
       "1  100002   3   2   2     8    489         NaN          NaN          NaN   \n",
       "2  100003   1  -1  -1     8     30         NaN          NaN          NaN   \n",
       "3  100006   1   2   1     8    545         NaN          NaN          NaN   \n",
       "4   10001  -1   1   2     8    244         NaN          NaN          NaN   \n",
       "\n",
       "   buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "0      NaN        NaN        NaN                NaN               NaN   \n",
       "1      NaN        NaN        NaN                NaN               NaN   \n",
       "2      NaN        NaN        NaN                NaN               NaN   \n",
       "3      NaN        NaN        NaN                NaN               NaN   \n",
       "4      NaN        NaN        NaN                NaN               NaN   \n",
       "\n",
       "   buy_click_ratio  buy_favor_ratio  comment_num  has_bad_comment  \\\n",
       "0              NaN              NaN          NaN              NaN   \n",
       "1              NaN              NaN          NaN              NaN   \n",
       "2              NaN              NaN          NaN              NaN   \n",
       "3              NaN              NaN          NaN              NaN   \n",
       "4              NaN              NaN          NaN              NaN   \n",
       "\n",
       "   bad_comment_rate  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_table = pd.read_csv(ITEM_TABLE_FILE)\n",
    "item_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据清洗"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 用户清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>105,321.000</td>\n",
       "      <td>105,318.000</td>\n",
       "      <td>105,318.000</td>\n",
       "      <td>105,321.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>105,180.000</td>\n",
       "      <td>72,129.000</td>\n",
       "      <td>105,172.000</td>\n",
       "      <td>103,197.000</td>\n",
       "      <td>45,986.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>252,661.000</td>\n",
       "      <td>2.773</td>\n",
       "      <td>1.113</td>\n",
       "      <td>3.850</td>\n",
       "      <td>180.466</td>\n",
       "      <td>5.471</td>\n",
       "      <td>2.434</td>\n",
       "      <td>0.459</td>\n",
       "      <td>1.045</td>\n",
       "      <td>291.222</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>30,403.698</td>\n",
       "      <td>1.672</td>\n",
       "      <td>0.956</td>\n",
       "      <td>1.072</td>\n",
       "      <td>273.437</td>\n",
       "      <td>10.618</td>\n",
       "      <td>5.600</td>\n",
       "      <td>1.048</td>\n",
       "      <td>3.442</td>\n",
       "      <td>460.031</td>\n",
       "      <td>0.270</td>\n",
       "      <td>0.022</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>200,001.000</td>\n",
       "      <td>-1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>226,331.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>40.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>59.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>252,661.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>94.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>148.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>278,991.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>212.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>342.000</td>\n",
       "      <td>0.167</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.001</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>305,321.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>7,605.000</td>\n",
       "      <td>369.000</td>\n",
       "      <td>231.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>99.000</td>\n",
       "      <td>15,302.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id         age         sex  user_lv_cd  browse_num  \\\n",
       "count 105,321.000 105,318.000 105,318.000 105,321.000 105,180.000   \n",
       "mean  252,661.000       2.773       1.113       3.850     180.466   \n",
       "std    30,403.698       1.672       0.956       1.072     273.437   \n",
       "min   200,001.000      -1.000       0.000       1.000       0.000   \n",
       "25%   226,331.000       3.000       0.000       3.000      40.000   \n",
       "50%   252,661.000       3.000       2.000       4.000      94.000   \n",
       "75%   278,991.000       4.000       2.000       5.000     212.000   \n",
       "max   305,321.000       6.000       2.000       5.000   7,605.000   \n",
       "\n",
       "       addcart_num  delcart_num     buy_num   favor_num   click_num  \\\n",
       "count  105,180.000  105,180.000 105,180.000 105,180.000 105,180.000   \n",
       "mean         5.471        2.434       0.459       1.045     291.222   \n",
       "std         10.618        5.600       1.048       3.442     460.031   \n",
       "min          0.000        0.000       0.000       0.000       0.000   \n",
       "25%          0.000        0.000       0.000       0.000      59.000   \n",
       "50%          2.000        0.000       0.000       0.000     148.000   \n",
       "75%          6.000        3.000       1.000       0.000     342.000   \n",
       "max        369.000      231.000      50.000      99.000  15,302.000   \n",
       "\n",
       "       buy_addcart_ratio  buy_browse_ratio  buy_click_ratio  buy_favor_ratio  \n",
       "count         72,129.000       105,172.000      103,197.000       45,986.000  \n",
       "mean               0.147             0.005            0.009            0.552  \n",
       "std                0.270             0.022            0.074            0.473  \n",
       "min                0.000             0.000            0.000            0.000  \n",
       "25%                0.000             0.000            0.000            0.000  \n",
       "50%                0.000             0.000            0.000            1.000  \n",
       "75%                0.167             0.002            0.001            1.000  \n",
       "max                1.000             1.000            1.000            1.000  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_user = pd.read_csv('data/User_table.csv',header=0)\n",
    "pd.options.display.float_format = '{:,.3f}'.format  #输出格式设置，保留三位小数\n",
    "df_user.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由上述统计信息发现： 第一行中根据User_id统计发现有105321个用户，发现有3个用户没有age,sex字段，而且根据浏览、加购、删购、购买等记录却只有105180条记录，说明存在用户无任何交互记录，因此可以删除上述用户。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "删除没有age,sex字段的用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34072</th>\n",
       "      <td>234073</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1</td>\n",
       "      <td>32.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>41.000</td>\n",
       "      <td>0.167</td>\n",
       "      <td>0.031</td>\n",
       "      <td>0.024</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38905</th>\n",
       "      <td>238906</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1</td>\n",
       "      <td>171.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>464.000</td>\n",
       "      <td>0.667</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67704</th>\n",
       "      <td>267705</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1</td>\n",
       "      <td>342.000</td>\n",
       "      <td>18.000</td>\n",
       "      <td>8.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>743.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  age  sex  user_lv_cd  browse_num  addcart_num  delcart_num  \\\n",
       "34072   234073  nan  nan           1      32.000        6.000        4.000   \n",
       "38905   238906  nan  nan           1     171.000        3.000        2.000   \n",
       "67704   267705  nan  nan           1     342.000       18.000        8.000   \n",
       "\n",
       "       buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "34072    1.000      0.000     41.000              0.167             0.031   \n",
       "38905    2.000      3.000    464.000              0.667             0.012   \n",
       "67704    0.000      0.000    743.000              0.000             0.000   \n",
       "\n",
       "       buy_click_ratio  buy_favor_ratio  \n",
       "34072            0.024            1.000  \n",
       "38905            0.004            0.667  \n",
       "67704            0.000              nan  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user[df_user['age'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "delete_list = df_user[df_user['age'].isnull()].index\n",
    "df_user.drop(delete_list,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "删除无交互记录的用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "105177\n"
     ]
    }
   ],
   "source": [
    "#删除无交互记录的用户\n",
    "df_naction = df_user[(df_user['browse_num'].isnull()) & (df_user['addcart_num'].isnull()) & (df_user['delcart_num'].isnull()) & (df_user['buy_num'].isnull()) & (df_user['favor_num'].isnull()) & (df_user['click_num'].isnull())]\n",
    "df_user.drop(df_naction.index,axis=0,inplace=True)\n",
    "print (len(df_user))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "统计并删除无购买记录的用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "75694\n"
     ]
    }
   ],
   "source": [
    "#统计无购买记录的用户\n",
    "df_bzero = df_user[df_user['buy_num']==0]\n",
    "#输出购买数为0的总记录数\n",
    "print (len(df_bzero))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除无购买记录的用户\n",
    "df_user = df_user[df_user['buy_num']!=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "      <td>29,483.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>250,746.445</td>\n",
       "      <td>2.914</td>\n",
       "      <td>1.025</td>\n",
       "      <td>4.272</td>\n",
       "      <td>302.488</td>\n",
       "      <td>10.525</td>\n",
       "      <td>4.673</td>\n",
       "      <td>1.637</td>\n",
       "      <td>1.677</td>\n",
       "      <td>486.653</td>\n",
       "      <td>0.360</td>\n",
       "      <td>0.018</td>\n",
       "      <td>0.030</td>\n",
       "      <td>0.862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29,979.676</td>\n",
       "      <td>1.490</td>\n",
       "      <td>0.959</td>\n",
       "      <td>0.808</td>\n",
       "      <td>391.535</td>\n",
       "      <td>14.301</td>\n",
       "      <td>7.568</td>\n",
       "      <td>1.412</td>\n",
       "      <td>4.584</td>\n",
       "      <td>658.671</td>\n",
       "      <td>0.320</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.136</td>\n",
       "      <td>0.287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>200,001.000</td>\n",
       "      <td>-1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>225,058.500</td>\n",
       "      <td>3.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>76.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>116.000</td>\n",
       "      <td>0.118</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.002</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>249,144.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>178.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>282.000</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>276,252.500</td>\n",
       "      <td>4.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>381.000</td>\n",
       "      <td>13.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>604.000</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.018</td>\n",
       "      <td>0.012</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>305,318.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>7,605.000</td>\n",
       "      <td>288.000</td>\n",
       "      <td>178.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>96.000</td>\n",
       "      <td>15,302.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id        age        sex  user_lv_cd  browse_num  addcart_num  \\\n",
       "count  29,483.000 29,483.000 29,483.000  29,483.000  29,483.000   29,483.000   \n",
       "mean  250,746.445      2.914      1.025       4.272     302.488       10.525   \n",
       "std    29,979.676      1.490      0.959       0.808     391.535       14.301   \n",
       "min   200,001.000     -1.000      0.000       2.000       1.000        0.000   \n",
       "25%   225,058.500      3.000      0.000       4.000      76.000        3.000   \n",
       "50%   249,144.000      3.000      1.000       4.000     178.000        6.000   \n",
       "75%   276,252.500      4.000      2.000       5.000     381.000       13.000   \n",
       "max   305,318.000      6.000      2.000       5.000   7,605.000      288.000   \n",
       "\n",
       "       delcart_num    buy_num  favor_num  click_num  buy_addcart_ratio  \\\n",
       "count   29,483.000 29,483.000 29,483.000 29,483.000         29,483.000   \n",
       "mean         4.673      1.637      1.677    486.653              0.360   \n",
       "std          7.568      1.412      4.584    658.671              0.320   \n",
       "min          0.000      1.000      0.000      0.000              0.004   \n",
       "25%          0.000      1.000      0.000    116.000              0.118   \n",
       "50%          2.000      1.000      0.000    282.000              0.250   \n",
       "75%          6.000      2.000      1.000    604.000              0.500   \n",
       "max        178.000     50.000     96.000 15,302.000              1.000   \n",
       "\n",
       "       buy_browse_ratio  buy_click_ratio  buy_favor_ratio  \n",
       "count        29,483.000       29,483.000       29,483.000  \n",
       "mean              0.018            0.030            0.862  \n",
       "std               0.038            0.136            0.287  \n",
       "min               0.000            0.000            0.010  \n",
       "25%               0.004            0.002            1.000  \n",
       "50%               0.008            0.005            1.000  \n",
       "75%               0.018            0.012            1.000  \n",
       "max               1.000            1.000            1.000  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "删除爬虫及惰性用户"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由上表所知，浏览购买转换比和点击购买转换比均值为0.018,0.030，因此这里认为浏览购买转换比和点击购买转换比小于0.0005的用户为惰性用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "90\n"
     ]
    }
   ],
   "source": [
    "bindex = df_user[df_user['buy_browse_ratio']<0.0005].index\n",
    "print (len(bindex))\n",
    "df_user.drop(bindex,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "323\n"
     ]
    }
   ],
   "source": [
    "cindex = df_user[df_user['buy_click_ratio']<0.0005].index\n",
    "print (len(cindex))\n",
    "df_user.drop(cindex,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "      <td>29,070.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>250,767.099</td>\n",
       "      <td>2.910</td>\n",
       "      <td>1.028</td>\n",
       "      <td>4.268</td>\n",
       "      <td>280.260</td>\n",
       "      <td>10.145</td>\n",
       "      <td>4.457</td>\n",
       "      <td>1.644</td>\n",
       "      <td>1.589</td>\n",
       "      <td>447.113</td>\n",
       "      <td>0.364</td>\n",
       "      <td>0.019</td>\n",
       "      <td>0.031</td>\n",
       "      <td>0.866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29,998.870</td>\n",
       "      <td>1.492</td>\n",
       "      <td>0.959</td>\n",
       "      <td>0.809</td>\n",
       "      <td>325.129</td>\n",
       "      <td>13.443</td>\n",
       "      <td>6.998</td>\n",
       "      <td>1.420</td>\n",
       "      <td>4.294</td>\n",
       "      <td>530.994</td>\n",
       "      <td>0.320</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.137</td>\n",
       "      <td>0.282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>200,001.000</td>\n",
       "      <td>-1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>225,036.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>75.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>114.000</td>\n",
       "      <td>0.125</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.002</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>249,200.500</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>174.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>275.000</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>276,284.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>366.000</td>\n",
       "      <td>13.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>585.000</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.018</td>\n",
       "      <td>0.012</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>305,318.000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>5,007.000</td>\n",
       "      <td>288.000</td>\n",
       "      <td>158.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>69.000</td>\n",
       "      <td>8,156.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id        age        sex  user_lv_cd  browse_num  addcart_num  \\\n",
       "count  29,070.000 29,070.000 29,070.000  29,070.000  29,070.000   29,070.000   \n",
       "mean  250,767.099      2.910      1.028       4.268     280.260       10.145   \n",
       "std    29,998.870      1.492      0.959       0.809     325.129       13.443   \n",
       "min   200,001.000     -1.000      0.000       2.000       1.000        0.000   \n",
       "25%   225,036.000      3.000      0.000       4.000      75.000        3.000   \n",
       "50%   249,200.500      3.000      1.000       4.000     174.000        6.000   \n",
       "75%   276,284.000      4.000      2.000       5.000     366.000       13.000   \n",
       "max   305,318.000      6.000      2.000       5.000   5,007.000      288.000   \n",
       "\n",
       "       delcart_num    buy_num  favor_num  click_num  buy_addcart_ratio  \\\n",
       "count   29,070.000 29,070.000 29,070.000 29,070.000         29,070.000   \n",
       "mean         4.457      1.644      1.589    447.113              0.364   \n",
       "std          6.998      1.420      4.294    530.994              0.320   \n",
       "min          0.000      1.000      0.000      0.000              0.004   \n",
       "25%          0.000      1.000      0.000    114.000              0.125   \n",
       "50%          2.000      1.000      0.000    275.000              0.250   \n",
       "75%          6.000      2.000      1.000    585.000              0.500   \n",
       "max        158.000     50.000     69.000  8,156.000              1.000   \n",
       "\n",
       "       buy_browse_ratio  buy_click_ratio  buy_favor_ratio  \n",
       "count        29,070.000       29,070.000       29,070.000  \n",
       "mean              0.019            0.031            0.866  \n",
       "std               0.038            0.137            0.282  \n",
       "min               0.001            0.001            0.018  \n",
       "25%               0.004            0.002            1.000  \n",
       "50%               0.008            0.005            1.000  \n",
       "75%               0.018            0.012            1.000  \n",
       "max               1.000            1.000            1.000  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最后这29070个用户为最终预测用户数据集"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
